//
//  SQLIiteManager.swift
//  SQLiteDemo
//
//  Created by Romeo on 15/9/17.
//  Copyright © 2015年 itheima. All rights reserved.
//

import Foundation

/*
    c语言框架 函数绝大数 都是以sqlite3_
*/

//数据库管理器
class SQLiteManager {
    
    //提供一个对外访问的单例对象 后续数据库相关的操作都依赖该句柄
    var db: COpaquePointer = nil
    static let sharedManager = SQLiteManager()
    
    //打开数据库
    func openDB(dbName: String){
        //fileName  数据库存储的完整路径  一般存储在 Doc/Caches
        //ppDb: 数据库操作句柄
        //会到当前路径打开数据文件 如果已经存在  不会重复创建  不存在就创建数据库文件
        let filePath = (NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).last! as NSString).stringByAppendingPathComponent(dbName)
        if sqlite3_open(filePath, &db) != SQLITE_OK {
            print("打开数据库失败")
            return
        }
        
        let result = createTable()
        let tip = result ? "创表成功" : "创表失败"
        
        print("\(tip) \(filePath)")
    }
    
    
    func beginTranscation() -> Bool {
        let sql = "BEGIN TRANSACTION;"
        return execSQL(sql)
    }
    
    func commitTtansaction() -> Bool {
        let sql = "COMMIT TRANSACTION;"
        return execSQL(sql)
    }
    
    func rollBackTranscation() -> Bool {
        let sql = "ROLLBACK TRANSACTION;"
        return execSQL(sql)
    }
    
    //查询数据 是为了获取一堆结果集
    func execRecordSet(sql: String) -> [AnyObject]? {
        //1.编译 sql 语句
        /*
            3> 表示长度  指 sql 语句的长度 -1 自动匹配长度
        */
//        "SELECT name,age,height FROM T_Person;"
        var stmt: COpaquePointer = nil
        if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
            print("sql语句编译失败")
            return nil
        }
        
        var list = [[String : AnyObject]]()
        while sqlite3_step(stmt) == SQLITE_ROW {
            //获取一行数据
            let dict = execRecordDict(stmt)
            
            list.append(dict)
        }
        
        //释放操作语句
        sqlite3_finalize(stmt)
        
        return list
    }
    
    private func execRecordDict(stmt: COpaquePointer) -> [String : AnyObject]{
        //获取到每行数据
        var dict = [String : AnyObject]()
        let colNum = sqlite3_column_count(stmt)
        for i in 0..<colNum {
            let cName = sqlite3_column_name(stmt, i)
            //获取每列字段
            let name = String(CString: cName, encoding: NSUTF8StringEncoding)!
            let type = sqlite3_column_type(stmt, i)
            switch type {
            case SQLITE_INTEGER:
                let iNum = Int(sqlite3_column_int64(stmt, i))
                dict[name] = iNum
            case SQLITE_FLOAT:
                let dNum = sqlite3_column_double(stmt, i)
                dict[name] = dNum
            case SQLITE_TEXT:
                let cStr = UnsafePointer<Int8>(sqlite3_column_text(stmt, i))
                let str = String(CString: cStr, encoding: NSUTF8StringEncoding)
                dict[name] = str
            case SQLITE_NULL:
                dict[name] = NSNull()
            default:
                print("不支持的数据类型")
            }
        }
        //获取到完整的字典信息
        return dict
    }
    
    
    //预编译 sql 语句 执行数据更新操作
    func prepareExecSQL(sql: String,parmas: CVarArgType...) -> Bool{
        //预编译 sql
        var stmt: COpaquePointer = nil
        if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) != SQLITE_OK {
            print("编译失败")
            return false
        }
        
        /*
        sqlite3_bind
        1>待执行的 sql 编译对象
        2>表示要绑定可接收参数的索引 默认是从1开始
        3>要绑定的值
        */
        
        var result = true
        var index: Int32 = 1
        
        for obj in parmas {
            //将盲对象转换成明确的对象
            if obj is Int {
                sqlite3_bind_int64(stmt, index, sqlite3_int64(obj as! Int))
            } else if obj is Double{
                sqlite3_bind_double(stmt, index, obj as! Double)
            } else  if obj is String {
                let cstr = (obj as! String).cStringUsingEncoding(NSUTF8StringEncoding)!
                sqlite3_bind_text(stmt, index, cstr, -1, SQLITE_TRANSIENT)
            } else if obj is NSNull {
                sqlite3_bind_null(stmt, index)
            } else {
                print("数据类型错误!")
            }
            index++
        }
        
        //数据顺序批量操作
        if sqlite3_step(stmt) != SQLITE_DONE {
            result = false
            print("插入失败")
        }
        //需要对 sql 语句进行复位操作 
        if sqlite3_reset(stmt) != SQLITE_OK {
            result = false
            print("复位语句错误")
        }
        
        //stmt核心对象使用完后  一定要释放  否则会造成内存泄露
        sqlite3_finalize(stmt)
        
        return result
    }
    private let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)
    
    //z执行 sql 语句
    func execSQL(sql: String) -> Bool {
        /*
            1>数据库操作句柄
        */
        return sqlite3_exec( db, sql, nil, nil, nil) == SQLITE_OK
    }
    
    func createTable() -> Bool {
        let path = (NSBundle.mainBundle().bundlePath as NSString).stringByAppendingPathComponent("status.sql")
        let data = NSData(contentsOfFile: path)!
        let sql = String(data: data, encoding: NSUTF8StringEncoding)!
        print(sql)
        
        return execSQL(sql)
    }
}